﻿using DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using DBUtility;

namespace DAL
{
    public class ICEntrustDAL
    {
        /// <summary>
        /// 获取委外入库单列表
        /// </summary>
        public DataSet GetICEntrust()
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select ROW_NUMBER() OVER (ORDER BY FBrNo)Num,FBrNo, ");
            commandText.Append(" CONVERT(varchar(100), Fdate, 23)Fdate,b.NAME FBillerID ");
            commandText.Append(" from ICEntrust left join sys_master b on b.ID = FBillerID ");
            commandText.Append(" where FStatus in (1,2) order by FBrNo ");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取委外入库单里的明细
        /// </summary>
        public DataSet GetICEntrustEntry(string FBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select FEntryNo,PkgsBarCode,a.PlaceBarCode,a.FName PlaceName  ");
            commandText.Append(" from ICEntrustDetail left join t_StockPlace a on a.FSPID = PosiID ");
            commandText.Append(" where FBrNo = '" + FBrNo + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// 获取库位名称
        /// </summary>
        public bool GetPlaceName(string PlaceBarCode, out string PlaceName)
        {
            bool lb_return = false;
            PlaceName = "";

            DataSet dt = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.Append(" select FNumber from t_StockPlace where PlaceBarCode = '" + PlaceBarCode + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    PlaceName = dr[0].ToString().Trim();
                }
            }

            if (PlaceName == null || PlaceName == "") { lb_return = false; } else { lb_return = true; }

            return lb_return;
        }
        /// <summary>
        /// 委外更新明细库位信息
        /// </summary>
        public bool UpdateICEntrustEntry(string FEntryNo, string PkgsBarCode, string PlaceBarCode)
        {
            bool lb_return = false;
            string stockID = ""; string PosiID = "";

            lb_return = GetStockPosi(PlaceBarCode, out stockID, out PosiID);

            if (lb_return)
            {
                StringBuilder commandText = new StringBuilder();
                commandText.Append(" update ICEntrustDetail set ");
                commandText.Append(" PkgsBarCode='" + PkgsBarCode + "'");
                commandText.Append(" ,StockID='" + stockID + "'");
                commandText.Append(" ,PosiID='" + PosiID + "'");
                commandText.Append(" where FEntryNo='" + FEntryNo + "'");

                try
                {
                    DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                    int i = DbHelperSQL.ExecuteSql(commandText.ToString());
                    if (i > 0)
                    { lb_return = true; }
                    else { lb_return = false; }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return lb_return;
        }
        //获取仓库  库位
        public bool GetStockPosi(string PlaceBarCode, out string stockID, out string PosiID)
        {
            bool lb_return = false;
            stockID = ""; PosiID = "";
            DataSet dt = new DataSet();
            StringBuilder sql = new StringBuilder();
            sql.Append(" select FParentID,FSPID from t_StockPlace where PlaceBarCode = '" + PlaceBarCode + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    stockID = dr[0].ToString().Trim();
                    PosiID = dr[1].ToString().Trim();
                }
            }

            return lb_return;
        }
        //获取层
        public bool GetFloor(string PlaceBarCode, out string Floor)
        {
            bool lb_return = false;
            Floor = "";

            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select MAX(a.Floor) Floor");
            commandText.Append(" from stk_Package a , t_StockPlace b ");
            commandText.Append(" where a.PkgsStatus in ('0','1')");
            commandText.Append(" and a.PosiID = b.FSPID ");
            commandText.Append(" and b.PlaceBarCode = '" + PlaceBarCode + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                lb_return = true;

                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    Floor = dr[0].ToString().Trim();
                }
            }

            return lb_return;
        }


        /// <summary>
        /// 判断是否可以完成委外入库
        /// </summary>
        /// <returns></returns>
        public bool IsFinishEntrust(string fBrNo, out string txt)
        {
            txt = "";
            //先判断审核了没有
            //if (IsEntrustExamine(fBrNo)) { } else { txt = "委外入库不是已审核状态"; return false; }
            //判断明细是否都有库位及数量
            //if (IsEntrustDetail(fBrNo)) { } else { txt = "委外入库明细录入不正确"; return false; }
            return true;
        }
        //判断委外单状态
        public bool IsEntrustExamine(string fBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            string ls_status = "";
            commandText.Append(" select FStatus from ICEntrust ");
            commandText.Append(" where FBrNo='" + fBrNo + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                ls_status = dr[0].ToString().Trim();
            }

            if (ls_status == "2") { } else { return false; }

            return true;
        }
        //判断委外单明细是否都有库位
        public bool IsEntrustDetail(string fBrNo)
        {
            DataSet ds = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select StockID,PosiID,Floor,FAuxqty from ICEntrustDetail ");
            commandText.Append(" where FBrNo='" + fBrNo + "'");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                ds = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (ds != null)
            {
                foreach (DataTable dt in ds.Tables)   //遍历所有的datatable
                {

                    foreach (DataRow dr in dt.Rows)
                    {
                        string StockID = dr["StockID"].ToString().Trim(); if (StockID == null || StockID == "" || StockID == "0") { return false; }
                        string PosiID = dr["PosiID"].ToString().Trim(); if (PosiID == null || PosiID == "" || PosiID == "0") { return false; }
                        string Floor = dr["Floor"].ToString().Trim(); if (Floor == null || Floor == "" || Floor == "0") { return false; }
                        string FAuxqty = dr["FAuxqty"].ToString().Trim(); if (FAuxqty == null || FAuxqty == "" || FAuxqty == "0") { return false; }
                    }
                }
            }
            else
            {
                return false;
            }


            return true;
        }
        /// <summary>
        /// 完成委外单
        /// </summary>
        /// <returns></returns>
        public bool FinishEntrust(string fBrNo, string InStkMan)
        {
            bool lb_return = false;
            DataSet dD = GetEntrustFinishDetail(fBrNo);
            string Time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            List<string> sql = new List<string>();

            //主、细表
            if (dD != null)
            {
                foreach (DataTable dt in dD.Tables)   //遍历所有的datatable
                {
                    int num = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        double row_number = double.Parse(dt.Rows[i]["row_number"].ToString().Trim());
                        string NFBrNo = DateTime.Now.AddSeconds(row_number).ToString("yyyyMMddHHmmss").Substring(2, 12);

                        string FItemID = dt.Rows[i]["FItemID"].ToString().Trim();
                        string SKU = dt.Rows[i]["ItemSku"].ToString().Trim();
                        string FKFDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        string FBatchNo = dt.Rows[i]["FGMPBatchNo"].ToString().Trim();
                        string FQty = dt.Rows[i]["FAuxqty"].ToString().Trim();
                        string FUnit = dt.Rows[i]["FUnit"].ToString().Trim();
                        string FModel = dt.Rows[i]["FModel"].ToString().Trim();
                        string Fname = dt.Rows[i]["FName"].ToString().Trim();

                        string ItemSku = dt.Rows[i]["ItemSku"].ToString().Trim();
                        string StickLen = dt.Rows[i]["FAuxqty"].ToString().Trim();
                        string Stick = "1";
                        string StockID = dt.Rows[i]["StockID"].ToString().Trim();
                        string PosiID = dt.Rows[i]["PosiID"].ToString().Trim();

                        string Floor = "";// dt.Rows[i]["Floor"].ToString().Trim(); 改为计算层
                        if (i == 0)
                        {
                            Floor = GetMaxFloor(StockID, PosiID);
                            num = 1;
                        }
                        else
                        {
                            string OStockID = dt.Rows[i - 1]["StockID"].ToString().Trim();
                            if (OStockID == StockID)
                            {
                                Floor = (int.Parse(GetMaxFloor(StockID, PosiID)) + num).ToString();
                                num++;
                            }
                            else
                            {
                                Floor = GetMaxFloor(StockID, PosiID);
                                num = 1;
                            }
                        }

                        string InStkTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                        string PkgsStatus = "0";
                        string dc = "1";
                        string EntrustBillNo = fBrNo;
                        string PkgsBarCode = dt.Rows[i]["PkgsBarCode"].ToString().Trim();


                        StringBuilder commandM = new StringBuilder();
                        commandM.Append(" insert into ICInventory(FBrNo,FItemID,SKU,FKFDate,FBatchNo,FQty,FQtyLock,FUnit,PieceNum,FModel,Fname)values(");
                        commandM.Append("'" + NFBrNo + "',"); commandM.Append("'" + FItemID + "',");
                        commandM.Append("'" + SKU + "',"); commandM.Append("'" + FKFDate + "',");
                        commandM.Append("'" + FBatchNo + "',"); commandM.Append("'" + FQty + "',");
                        commandM.Append("'0',"); commandM.Append("'" + FUnit + "',");
                        commandM.Append("'1',"); commandM.Append("'" + FModel + "',");
                        commandM.Append("'" + Fname + "')");

                        StringBuilder commandD = new StringBuilder();
                        commandD.Append(" insert into stk_Package(FBrNo,ItemSku,StickLen,Stick,FQty,StockID,PosiID,Floor,");
                        commandD.Append(" InStkTime,InStkMan,PkgsStatus,EntrustBillNo,dc,PkgsBarCode)values(");

                        commandD.Append("'" + NFBrNo + "',"); commandD.Append("'" + ItemSku + "',");
                        commandD.Append("'" + StickLen + "',"); commandD.Append("'" + Stick + "',");
                        commandD.Append("'" + FQty + "',"); commandD.Append("'" + StockID + "',");
                        commandD.Append("'" + PosiID + "',"); commandD.Append("'" + Floor + "',");
                        commandD.Append("'" + InStkTime + "',"); commandD.Append("'" + InStkMan + "',");
                        commandD.Append("'" + PkgsStatus + "',"); commandD.Append("'" + EntrustBillNo + "',");
                        commandD.Append("'" + dc + "',"); commandD.Append("'" + PkgsBarCode + "')");

                        sql.Add(commandM.ToString());
                        sql.Add(commandD.ToString());
                    }
                }
            }

            sql.Add("update ICEntrust set FStatus=3 ,FExamineID='" + InStkMan + "',FExamineDate='" + Time + "' where FBrNo = '" + fBrNo + "'");

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                int i = 0;
                i = DbHelperSQL.ExecuteSqlTran(sql);
                if (i > 0)
                { lb_return = true; }
                else
                { lb_return = false; }

            }
            catch (Exception ex)
            {
                throw ex;
            }


            return lb_return;
        }
        /// <summary>
        /// 获取委外单
        /// </summary>
        /// <returns></returns>
        public DataSet GetEntrustFinishDetail(string FBrNo)
        {
            DataSet dt = new DataSet();
            StringBuilder commandText = new StringBuilder();
            commandText.Append(" select row_number() over(order by FEntryNo) as row_number, ");
            commandText.Append(" FEntryNo,FBrNo,FItemID,FNumber,FName,FAttrID,FAttr,ItemSku,FModel, ");
            commandText.Append(" FUnit,FGMPBatchNo,FAuxqty,FAuxQtyMust,FAmount,StockID,PosiID,Floor, ");
            commandText.Append(" FSourceBillNo,FMaterialPrice,FMaterialCost,FfetchFirm,FProcessCost, ");
            commandText.Append(" FTaxRate,FTaxAmount,FTicket,FNote,PkgsBarCode ");
            commandText.Append(" from ICEntrustDetail ");
            commandText.Append(" where FBrNo='" + FBrNo + "'");
            commandText.Append(" order by FEntryNo ");
            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        /// <summary>
        /// 计算层
        /// </summary>
        /// <returns></returns>
        public string GetMaxFloor(string StockID, string PosiID)
        {
            string ls_return = "";
            string floor = "";
            DataSet dt = new DataSet();

            StringBuilder commandText = new StringBuilder();

            commandText.Append(" select MAX(FLOOR) FLOOR ");
            commandText.Append(" from stk_Package ");
            commandText.Append(" where PkgsStatus in (0,1) ");
            commandText.Append(" and StockID = " + StockID);
            commandText.Append(" and PosiID = " + PosiID);

            try
            {
                DbHelperSQL.connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                dt = DbHelperSQL.Query(commandText.ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (dt != null)
            {
                foreach (DataRow dr in dt.Tables[0].Rows)
                {
                    floor = dr[0].ToString().Trim();
                }
            }
            if (floor == "" || floor == null)
            {
                ls_return = "1";
            }
            else
            {
                int maxfloor = 0;
                maxfloor = int.Parse(floor) + 1;
                ls_return = maxfloor.ToString();
            }

            return ls_return;
        }

    }
}
